package package02;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @Description TODO
 * @Author zzj
 * @Date 2020/7/16 16:51
 */
public class EmpDaoImp implements EmpDao {
    private DBUtil dbUtil = new DBUtil();
    private PreparedStatement pst;
    private ResultSet rs;

    @Override
    public List<Emp> findAll() {
        List<Emp> emps = new ArrayList<>();
        // 获取会话
        Connection conn = dbUtil.init();
        try {
            // 声明sql
            String sql = "select * from emp";
            // 创建statement
            Statement statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            while (rs.next()) {
                Emp emp = createEmp(rs);
                emps.add(emp);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            dbUtil.close();
        }
        return emps;
    }

    @Override
    public Emp findById(Integer id) {
        Emp emp = null;
        // 获取会话
        Connection conn = dbUtil.init();
        // 声明sql语句
        String sql = "select * from emp where empno = ?";
        // 创建statement
        try {
            pst = conn.prepareStatement(sql);
            pst.setInt(1, id);
            // 执行查询
            rs = pst.executeQuery();
            while (rs.next()) {
                emp = createEmp(rs);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        return emp;
    }

    @Override
    public int findCount() {
        int count = 0;
        // 获取会话
        Connection conn = dbUtil.init();
        // 定义SQL
        String sql = "select count(1) from emp";
        // 获取statement
        try {
            Statement statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            while (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return count;
    }

    @Override
    public List<Emp> findAllByPage(int current, int size) {
        ArrayList<Emp> emps = new ArrayList<>();
        // 获取会话
        Connection conn = dbUtil.init();
        // 定义SQL
        String sql = "select * from \n" +
                "(select a.*,rownum rn from \n" +
                "(select e.* from emp e order by ename)a where rownum <= ?)b where b.rn >?";
        int start = (current - 1) * size;
        int end = current * size;
        // 获取statement
        try {
            pst = conn.prepareStatement(sql);
            pst.setInt(1, end);
            pst.setInt(2, start);
            rs = pst.executeQuery();
            while (rs.next()) {
                Emp emp = createEmp(rs);
                emps.add(emp);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return emps;
    }


    @Override
    public void save(Emp emp) {

        // 获取会话
        Connection connection = dbUtil.init();
        // 声明sql语句
        String sql = "insert into emp values(?,?,?,?,?,?,?,?)";
        try {
            // 预编译sql
            pst = connection.prepareStatement(sql);
            // 设定参数
            pst.setInt(1, emp.getEmpNo());
            pst.setString(2, emp.geteName());
            pst.setString(3, emp.getJob());
            pst.setInt(4, emp.getMgr());
            pst.setDate(5, new Date(emp.getHireDate().getTime()));
            pst.setDouble(6, emp.getSal());
            pst.setDouble(7, emp.getComm());
            pst.setInt(8, emp.getDeptNo());
            // 执行更新表（插入）
            int count = pst.executeUpdate(); // 受影响的行数
            System.out.println(count);// 1
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbUtil.close();
        }

    }

    @Override
    public void deleteByID(Integer id) {
        // 获取数据库会话
        Connection connection = dbUtil.init();
        // 声明sql
        String sql = "delete emp where empno = ?";
        try {
            // 预编译sql
            pst = connection.prepareStatement(sql);
            // 设定参数
            pst.setInt(1, id);
            // 执行更新表（删除）
            int count = pst.executeUpdate();
            System.out.println(count);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbUtil.close();
        }

    }

    @Override
    public void updateById(Emp emp) {
        // 获取会话
        Connection connection = dbUtil.init();
        // 声明sql
        String sql = "update emp set sal = ?  where empno = ?";
        try {
            // 预编译sql
            pst = connection.prepareStatement(sql);
            // 设定参数
            pst.setDouble(1, emp.getSal());
            pst.setInt(2, emp.getEmpNo());
            // 执行更新表 （更新行）
            int count = pst.executeUpdate();
            System.out.println(count);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbUtil.close();
        }
    }

    @Override
    public List<Emp> findAll(Emp emp) {
        List<Emp> emps = new ArrayList<>();
        // 获取会话
        Connection connection = dbUtil.init();
        // 声明 sql 的 基本部分 select * from emp
        StringBuilder baseSql = new StringBuilder("select * from emp where ");
        // 根据emp对象进行条件封装 ,当对象的成员变量为 null 则没有这个条件
        List<Object> params = new ArrayList<>();

        if (emp.geteName() != null) {
            baseSql.append(" ename = ? and ");
            params.add(emp.geteName());
        }
        if (emp.getJob() != null) {
            baseSql.append(" job = ? and ");
            params.add(emp.getJob());
        }
        if (emp.getMgr() != null) {
            baseSql.append(" mgr = ? and ");
            params.add(emp.getMgr());
        }
        if (emp.getHireDate() != null) {
            baseSql.append(" hiredate = ? and ");
            params.add(emp.getHireDate());
        }
        if (emp.getSal() != null) {
            baseSql.append(" sal = ? and ");
            params.add(emp.getSal());
        }
        if (emp.getComm() != null) {
            baseSql.append(" comm = ? and ");
            params.add(emp.getComm());
        }
        if (emp.getDeptNo() != null) {
            baseSql.append(" deptno = ? and");
            params.add(emp.getDeptNo());
        }
        String sql = baseSql.toString();
        sql = sql.substring(0, sql.lastIndexOf("and"));
        System.out.println(sql);
        try {
            // 预编译
            pst = connection.prepareStatement(sql);
            // 设定参数
            for (int i = 0; i < params.size(); i++) {
                // 多态
                pst.setObject(i + 1, params.get(i));
            }

            rs = pst.executeQuery();
            while (rs.next()) {
                Emp e = createEmp(rs);
                emps.add(e);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbUtil.close();
        }
        return emps;
    }

    public void savePart(Emp emp) {
        // 获取会话
        Connection connection = dbUtil.init();
        // 声明 sql 的 基本部分 select * from emp
        StringBuilder baseSql = new StringBuilder("insert into emp ( ");
        // 根据emp对象进行条件封装 ,当对象的成员变量为 null 则没有这个条件
        List<Object> params = new ArrayList<>();
        if (emp.getEmpNo() != null) {
            baseSql.append(" empno, ");
            params.add(emp.getEmpNo());
        }
        if (emp.geteName() != null) {
            baseSql.append("ename, ");
            params.add(emp.geteName());
        }
        if (emp.getJob() != null) {
            baseSql.append("job, ");
            params.add(emp.getJob());
        }
        if (emp.getMgr() != null) {
            baseSql.append("mgr, ");
            params.add(emp.getMgr());
        }
        if (emp.getHireDate() != null) {
            baseSql.append("hiredate, ");
            params.add(new Date(emp.getHireDate().getTime()));
        }
        if (emp.getSal() != null) {
            baseSql.append("sal, ");
            params.add(emp.getSal());
        }
        if (emp.getComm() != null) {
            baseSql.append("comm, ");
            params.add(emp.getComm());
        }
        if (emp.getDeptNo() != null) {
            baseSql.append("deptno, ");
            params.add(emp.getDeptNo());
        }
        String sql = baseSql.toString();
        //System.out.println(sql);   // insert into emp ( job, hiredate, sal,
        sql = sql.substring(0, sql.lastIndexOf(","));
        //System.out.println(sql);

        StringBuilder builder2 = new StringBuilder(sql);
        //System.out.println(builder2.toString());
        builder2.append(") values (");
        //System.out.println(builder2.toString());  //insert into emp ( job, hiredate, sal) values (
        for (int i = 0; i < params.size(); i++) {
            builder2.append("?,");
        }
        //System.out.println(builder2.toString());  // insert into emp ( job, hiredate, sal) values (?,?,?,
        String sql2 = builder2.toString();
        sql2 = sql2.substring(0, sql2.lastIndexOf(","));
        //System.out.println(sql2);

        StringBuilder builder3 = new StringBuilder(sql2);
        builder3.append(")");
        String finalSql = builder3.toString();
        System.out.println(finalSql);
        try {
            // 预编译
            pst = connection.prepareStatement(finalSql);
            // 设定参数
            for (int i = 0; i < params.size(); i++) {
                // 多态
                pst.setObject(i + 1, params.get(i));
            }
            int count = pst.executeUpdate();
            System.out.println(count);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbUtil.close();
        }
    }

    private Emp createEmp(ResultSet rs) throws SQLException {
        // 每进入依次循环，会获取一条数据
        Emp emp = new Emp();
        emp.setEmpNo(rs.getInt(1));
        emp.seteName(rs.getString(2));
        emp.setJob(rs.getString(3));
        emp.setMgr(rs.getInt(4));
        emp.setHireDate(rs.getDate(5));
        emp.setSal(rs.getDouble(6));
        emp.setComm(rs.getDouble(7));
        emp.setDeptNo(rs.getInt(8));
        return emp;
    }
}
